﻿using System;
using System.Collections.Generic;
using System.Linq;
using System.Text;
using System.Data;
using System.Data.SqlClient;
using System.Configuration;
using AlexandriaPressAdminObject;

namespace AlexandriaPressAdminDataAccessLayer
{
    public class UserDL
    {
        private static string myConnectionString
        {
            get
            {
                return ConfigurationManager.ConnectionStrings["BookConn1"].ConnectionString;
            }
        }

        public static List<UserInfo> displayAll()
        {
            SqlConnection conn = new SqlConnection();
            conn.ConnectionString = myConnectionString;

            SqlCommand comm = new SqlCommand();
            comm.Connection = conn;
            comm.CommandType = CommandType.StoredProcedure;
            comm.CommandText = "usp_View_User_List";

            if (conn.State != ConnectionState.Open)
                conn.Open();

            SqlDataReader dtReader = comm.ExecuteReader(CommandBehavior.CloseConnection);
 
            List<UserInfo> lstOfUser = new List<UserInfo>();
            UserInfo User = null;

            while (dtReader.Read())
            {
                User = new UserInfo();
                User.ID = dtReader.GetInt32(dtReader.GetOrdinal("UserID"));
                User.Name = dtReader.GetString(dtReader.GetOrdinal("Full Name"));
                User.Email = dtReader.GetString(dtReader.GetOrdinal("Email"));
                User.DateJoined = dtReader.GetDateTime(dtReader.GetOrdinal("DateJoined"));
                User.ProfilePic = "~/MasterPages/UserPages/" + dtReader.GetString(dtReader.GetOrdinal("ProfilePicture"));
                lstOfUser.Add(User);
            }
            return lstOfUser;
        }

        public static int suspendUser(List<UserInfo> _UserList)
        {
            int count = 0;

            SqlConnection conn = new SqlConnection();
            conn.ConnectionString = myConnectionString;

            if (conn.State != ConnectionState.Open) conn.Open();

             SqlTransaction transaction = conn.BeginTransaction();

             try
             {

                 foreach (UserInfo User in _UserList)
                 {

                     SqlCommand SuspendUser = new SqlCommand();
                     SuspendUser.Connection = conn;
                     SuspendUser.CommandType = CommandType.StoredProcedure;
                     SuspendUser.CommandText = "usp_Suspend_User_Author";
                     SuspendUser.Transaction = transaction;

                     SqlParameter param1 = new SqlParameter();
                     param1.DbType = DbType.Int32;
                     param1.Value = User.ID;
                     param1.ParameterName = "@userId";
                     param1.Direction = ParameterDirection.Input;
                     SuspendUser.Parameters.Add(param1);

                     SqlParameter param2 = new SqlParameter();
                     param2.DbType = DbType.String;
                     param2.Value = User.Reason;
                     param2.ParameterName = "@reason";
                     param2.Direction = ParameterDirection.Input;
                     SuspendUser.Parameters.Add(param2);

                     SqlParameter param3 = new SqlParameter();
                     param3.DbType = DbType.DateTime;
                     param3.Value = User.EndOfSuspension;
                     param3.ParameterName = "@endSuspensionDate";
                     param3.Direction = ParameterDirection.Input;
                     SuspendUser.Parameters.Add(param3);
                                                              
                     int a = Convert.ToInt32(SuspendUser.ExecuteScalar());
                     count++;
                 }
                 transaction.Commit();
                 return count;
             }

             catch
             {

                 transaction.Rollback();
                 return 0;
             }
             finally
             {
                 conn.Close();
                 transaction.Dispose();
             }
        }


        public static int banUser(List<UserInfo> _UserList)
        {
            int count = 0;

            SqlConnection conn = new SqlConnection();
            conn.ConnectionString = myConnectionString;

            if (conn.State != ConnectionState.Open) conn.Open();

            SqlTransaction transaction = conn.BeginTransaction();

            try
            {

                foreach (UserInfo User in _UserList)
                {
                    SqlCommand BanUser = new SqlCommand();
                    BanUser.Connection = conn;
                    BanUser.CommandType = CommandType.StoredProcedure;
                    BanUser.CommandText = "usp_Ban_User_Author";
                    BanUser.Transaction = transaction;

                    SqlParameter param1 = new SqlParameter();
                    param1.DbType = DbType.Int32;
                    param1.Value = User.ID;
                    param1.ParameterName = "@userId";
                    param1.Direction = ParameterDirection.Input;
                    BanUser.Parameters.Add(param1);

                    SqlParameter param2 = new SqlParameter();
                    param2.DbType = DbType.String;
                    param2.Value = User.Reason;
                    param2.ParameterName = "@reasonForBan";
                    param2.Direction = ParameterDirection.Input;
                    BanUser.Parameters.Add(param2);

                    int a = Convert.ToInt32(BanUser.ExecuteScalar());
                    count++;
                }
                transaction.Commit();
                return count;
            }
            catch
            {
                
                transaction.Rollback();
                return 0;
            }
            finally
            {
                conn.Close();
                transaction.Dispose();
            }

        }
    }
}
